from database.mysqlhelper import MySqLHelper

db = MySqLHelper()

insert_sql = 'insert into t_funds_info_his (f_code,f_name,f_rq,f_ratio,f_hold_hard,f_stock_val,' \
             'f_fund_code,f_fund_name,f_total_val,f_type,f_short_name,f_full_name,f_attribute) ' \
             'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) '
select_sql = 'select f_code,f_name,f_rq,f_ratio,f_hold_hard,f_stock_val,' \
             'f_fund_code,f_fund_name,f_total_val,f_type,f_short_name,f_full_name,f_attribute,f_update_time from t_funds_info_his '


# 插入多条
def insert(info):
    # 增加多条
    return db.insertone(insert_sql, to_tuple(info))


# 转换为元组
def to_tuple(info):
    return (
        info['code'], info['name'], info['rq'], info['ratio'], info['hold_hard'], info['stock_val'], info['fund_code'],
        info['fund_name'], info['total_val'], info['type'], info['short_name'], info['full_name'],
        info['attribute'])


# 插入多条
def insert_mul(data_list, rq):
    # 增加多条
    li = []
    for info in data_list:
        info['rq'] = rq
        li.append(to_tuple(info))
    return db.insertmany(insert_sql, li)


# 所有股票
def selectall():
    return format_res(db.selectall(select_sql))


# 获取一条
def select_stock_code(fund_code):
    sql = select_sql + ' where f_code = %s '
    return format_res(db.selectall(sql, (fund_code)))


# 获取一条
def select_fund_one(fund_code):
    sql = select_sql + ' where f_fund_code = %s limit 1 '
    return format_one(db.selectone(sql, (fund_code)))


# 删除一版
def delete_fund(fund_code):
    sql = 'delete from t_funds_info_his where f_fund_code = %s '
    return db.delete(sql, (fund_code))


# 所有股票
def update_val(stock_code, fund_code, stock_val):
    sql1 = 'update t_funds_info_his set f_total_val = %s where f_code = %s and f_fund_code= %s '
    return db.update(sql1, (stock_val, stock_code, fund_code))


# 格式化结果
def format_one(res):
    if not res:
        return {}
    return {'code': str(res[0], encoding="utf-8"), 'name': str(res[1], encoding="utf-8"), 'rq': res[2],
            'ratio': float(res[3]), 'hold_hard': float(res[4]), 'stock_val': int(res[5]),
            'fund_code': str(res[6], encoding="utf-8"), 'fund_name': str(res[7], encoding="utf-8"),
            'total_val': int(res[8]), 'type': str(res[9], encoding="utf-8"),
            'short_name': str(res[10], encoding="utf-8"), 'full_name': str(res[11], encoding="utf-8"),
            'attribute': str(res[12], encoding="utf-8"), 'up_time': res[13]}


# 格式化结果
def format_res(res_list):
    list = []
    for res in res_list:
        list.append(format_one(res))
    return list
